﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace AndreySmirnov.BLToolkitUtils.VSPackage.Model.SchemaProvider
{
	class MSSQLSchemaProvider : ISchemaProvider
	{
		private ServerConnection m_connection;
		private Server m_server;
		private Database m_database;

		public void Connect(BLTConnectionInfo connectionInfo)
		{
			//TODO Add support for all cred formats supported by system
		    switch (connectionInfo.CredentialsType)
		    {
		        case BLTConnectionCredentialsType.ClearText:
		            var userName = CredentialHelpers.ParseConnectionData(connectionInfo.CredentialsData,
		                                                                 CredentialHelpers.USER_NAME_KEY);
		            var password = CredentialHelpers.ParseConnectionData(connectionInfo.CredentialsData,
		                                                                 CredentialHelpers.PASSWORD_KEY);
                    m_connection = new ServerConnection(connectionInfo.Server, userName, password);
                    break;
		        case BLTConnectionCredentialsType.Encrypted:
                    throw new NotImplementedException("Not yet implemented");
		            break;
                case BLTConnectionCredentialsType.Prompt:
                    throw new NotImplementedException("Not yet implemented");
		            break;
		        case BLTConnectionCredentialsType.SSPI:
                    m_connection = new ServerConnection(connectionInfo.Server);
		            break;
		        default:
		            throw new ArgumentOutOfRangeException();
		    }
		    m_connection.Connect();
			m_server = new Server(m_connection);
			m_database = m_server.Databases[connectionInfo.DatabaseName];
		}

        public IList<TableInfo> EnumTables()
		{
			return m_database.Tables
				.Cast<Table>()
				.Where(x => !x.IsSystemObject)
				.Select(x => new TableInfo
				                 {
				                     TableName = x.Name,
                                     SchemaName = x.Schema,
                                     IsDefaultSchema = x.Schema == "dbo"
				                 })
				.ToList();
		}

		private static DbType SqlDataTypeToDbType(SqlDataType sqlDataType)
		{
			switch (sqlDataType)
			{
				case SqlDataType.None:
					return DbType.Object;
				case SqlDataType.BigInt:
					return DbType.Int64;
				case SqlDataType.Binary:
					return DbType.Binary;
				case SqlDataType.Bit:
					return DbType.Boolean;
				case SqlDataType.Char:
					return DbType.String;
				case SqlDataType.DateTime:
					return DbType.DateTime;
				case SqlDataType.Decimal:
					return DbType.Decimal;
				case SqlDataType.Float:
					return DbType.Single;
				case SqlDataType.Image:
					return DbType.Binary;
				case SqlDataType.Int:
					return DbType.Int32;
				case SqlDataType.Money:
					return DbType.Currency;
				case SqlDataType.NChar:
					return DbType.String;
				case SqlDataType.NText:
                    return DbType.String;
				case SqlDataType.NVarChar:
                    return DbType.String;
				case SqlDataType.NVarCharMax:
                    return DbType.String;
				case SqlDataType.Real:
					return DbType.Double;
				case SqlDataType.SmallDateTime:
					return DbType.DateTime;
				case SqlDataType.SmallInt:
					return DbType.Int16;
				case SqlDataType.SmallMoney:
					return DbType.Currency;
				case SqlDataType.Text:
                    return DbType.String;
				case SqlDataType.Timestamp:
					return DbType.Binary;
				case SqlDataType.TinyInt:
					return DbType.Byte;
				case SqlDataType.UniqueIdentifier:
					return DbType.Guid;
				case SqlDataType.UserDefinedDataType:
					return DbType.Object;
				case SqlDataType.UserDefinedType:
                    return DbType.Object;
				case SqlDataType.VarBinary:
					return DbType.Binary;
				case SqlDataType.VarBinaryMax:
					return DbType.Binary;
				case SqlDataType.VarChar:
                    return DbType.String;
				case SqlDataType.VarCharMax:
					return DbType.String;
				case SqlDataType.Variant:
					return DbType.Object;
				case SqlDataType.Xml:
					return DbType.Xml;
				case SqlDataType.Numeric:
					return DbType.Int16;
				case SqlDataType.Date:
					return DbType.Date;
				case SqlDataType.Time:
					return DbType.Time;
				case SqlDataType.DateTimeOffset:
					return DbType.DateTimeOffset;
				case SqlDataType.DateTime2:
					return DbType.DateTime2;
				case SqlDataType.UserDefinedTableType:
					return DbType.Object;
				default:
					throw new ArgumentOutOfRangeException("sqlDataType");
			}
		}

        public IList<ForeignKeyInfo> GetTableForeignKeys(TableInfo table)
        {
            var smoTable = m_database.Tables
                .Cast<Table>()
                .FirstOrDefault(x => x.Name == table.TableName && x.Schema == table.SchemaName);
            if (smoTable == null)
                throw new Exception(string.Format("Unable to find table [{0}].[{1}]", table.SchemaName, table.TableName));

            var foreignKeys = new List<ForeignKeyInfo>();

            foreach (ForeignKey foreignKey in smoTable.ForeignKeys)
            {
                ForeignKey key = foreignKey;
                var pkTable = m_database.Tables
                    .Cast<Table>()
                    .FirstOrDefault(x => x.Name == key.ReferencedTable && x.Schema == key.ReferencedTableSchema);
                if (pkTable == null)
                {
                    throw new Exception(string.Format("Unable to locate referenced table {0}.{1}",
                                                      foreignKey.ReferencedTableSchema,
                                                      foreignKey.ReferencedTable));
                }
                var index = pkTable.Indexes[foreignKey.ReferencedKey];
                //making sure we have a column (sanity check)
                Debug.Assert(index.IndexedColumns.Count > 0);
                var pkColumn = index.IndexedColumns[0];

                //making sure we have a column (sanity check)
                Debug.Assert(foreignKey.Columns.Count > 0);

                var fkColumn = foreignKey.Columns[0];

                foreignKeys.Add(new ForeignKeyInfo
                {
                    PrimaryKeyTableName = pkTable.Name,
                    PrimaryKeyTableSchema = pkTable.Schema,
                    PrimaryKeyColumnName = pkColumn.Name,
                    ForeignKeyTableName = table.TableName,
                    ForeignKeyTableSchema = table.SchemaName,
                    ForeignKeyColumnName = fkColumn.Name,
                });
            }
            return foreignKeys;
        }

        public IList<FieldInfo> GetTableFields(TableInfo table)
		{
			var smoTable = m_database.Tables
                .Cast<Table>()
                .FirstOrDefault(x => x.Name == table.TableName && x.Schema == table.SchemaName);
            if (smoTable == null)
                throw new Exception(string.Format("Unable to find table [{0}].[{1}]", table.SchemaName, table.TableName));

            return smoTable.Columns
				.Cast<Column>()
				.Select(x => new FieldInfo
				             	{
				             		Name = x.Name,
				             		IsNullable = x.Nullable,
				             		IsPrimaryKey = x.InPrimaryKey,
                                    IsAutoGenerated = x.Identity,
				             		DbType = SqlDataTypeToDbType(x.DataType.SqlDataType),
									DbTypeSize = x.DataType.MaximumLength
				             	})
				.ToList();
		}

		#region Dispose

		~MSSQLSchemaProvider()
		{
			Dispose(false);
		}

		protected void Dispose(bool disposing)
		{
			if (disposing)
			{
				if (m_connection != null)
				{
					m_connection.Disconnect();
				}
			}
		}

		public void Dispose()
		{
			Dispose(true);
			GC.SuppressFinalize(this);
		}

		#endregion
	}
}
